<?php

namespace api\modules\backend\controllers;

use api\modules\backend\models\ApplyGift;
use api\modules\backend\models\Gift;
use api\modules\backend\models\Journey;
use api\modules\backend\models\Apply;
use api\modules\backend\models\JourneyGift;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use yii\data\Pagination;
use api\modules\backend\models\UploadForm;
use yii\web\UploadedFile;
use common\commands\Common;
use Yii;
use yii\filters\auth\QueryParamAuth;

/**
 * Apply Controller API
 *
 * @author Fong <xiaofeng.zhang@suppernovachina.com>
 */
class ApplyController extends ControllerBase
{
    public $modelClass = 'api\modules\v1\models\Journey';
    private $notDeleted = 0; //未删除状态
    private $deleted = 1; //已删除状态
    private $active = 1;

    public function behaviors()
    {
        $behaviors = parent::behaviors();
        $behaviors['authenticator'] = [
            'class' => QueryParamAuth::className(),
            'optional' => [],
        ];
        $behaviors['authenticator']['except'] = ['options'];

        return $behaviors;
    }

    /**
     * 报名列表
     */
    public function actionIndex()
    {
        $get = Yii::$app->request->get();

        $where = [
            'journey_id' => $get['journey_id'],
            'a.is_deleted' => $this->notDeleted
        ];
        $get['name'] = isset($get['name']) ? $get['name'] : '';
        $get['mobile'] = isset($get['mobile']) ? $get['mobile'] : '';
        $get['credentials_no'] = isset($get['credentials_no']) ? $get['credentials_no'] : '';

        $giftModelClass = new Gift();
        $giftQuery = $giftModelClass::find()->where(['is_deleted' => $this->notDeleted]);

        $applyModelClass = new Apply();
        $applyFields = array(
            'apply_id',
            'name',
            'mobile',
            'credentials',
            'credentials_no',
            'born',
            'gender',
            'gift_name',
            'a.create_time'
        );
        $applyQuery = $applyModelClass::find()->from('{{%apply}} as a')->select($applyFields)
            ->leftJoin('(' . $giftQuery->createCommand()->getRawSql() . ') as g', 'g.gift_id = a.gift_id')
            ->where($where)
            ->andwhere([
                'and',
                ['like', 'name', $get['name']],
                ['like', 'mobile', $get['mobile']],
                ['like', 'credentials_no', $get['credentials_no']],
            ]);

        $countQuery = clone $applyQuery;
        $pages = new Pagination(['totalCount' => $countQuery->count()]);
        $journey = $countQuery->offset($pages->offset)
            ->limit($pages->limit)
            ->orderBy(['create_time' => SORT_DESC])
            ->asArray()
            ->all();

        $meta = array('totalCount' => (int)$pages->totalCount, 'perPage' => $pages->getPageSize(), 'pageCount' => $pages->getPageCount(), 'currentPage' => $pages->getPage() + 1);
        $result = array('item' => $journey, '_meta' => $meta);

        $this->success($result);
    }

    /**
     * 行程列表
     */
    public function actionJourney()
    {
        $get = Yii::$app->request->get();

        $where = [
            '`j`.is_deleted' => $this->notDeleted,
            '`l`.is_deleted' => $this->notDeleted,
        ];
        $get['line_name'] = isset($get['line_name']) ? $get['line_name'] : '';
        $get['serial'] = isset($get['serial']) ? $get['serial'] : '';
        $get['departure_date_begin'] = isset($get['departure_date_begin']) ? $get['departure_date_begin'] : '';
        $get['departure_date_end'] = isset($get['departure_date_end']) ? $get['departure_date_end'] : '';
        if (isset($get['active']) && is_numeric($get['active'])) $where['j.active'] = $get['active'];

        $applyModelClass = new Apply();
        $applySql = $applyModelClass::find()->where(['is_deleted' => $this->notDeleted])->createCommand()->getRawSql();

        $journeyModelClass = new Journey();
        $journeyFields = array(
            '`j`.line_id',
            '`j`.journey_id',
            'line_name',
            'serial',
            'departure_date',
            'day',
            'max',
            'apply_amount',
            'line_active' => 'l.active',
            'journey_active' => 'j.active',
            'number' => 'COUNT(a.apply_id)'
        );
        $journeyQuery = $journeyModelClass::find()->from('{{%journey}} as j')->select($journeyFields)
            ->leftJoin('{{%line}} as l', 'l.line_id = j.line_id')
            ->leftJoin('(' . $applySql . ') as a', 'a.journey_id = j.journey_id')
            ->where($where)
            ->andwhere([
                'and',
                ['like', 'line_name', $get['line_name']],
                ['like', 'serial', $get['serial']],
            ])
            ->groupBy(['j.journey_id']);

        if ($get['departure_date_begin'] && $get['departure_date_end'] && $get['departure_date_begin'] <= $get['departure_date_end']) {
            $journeyQuery->andWhere(['between', 'departure_date', $get['departure_date_begin'], $get['departure_date_end']]);
        }

        $countQuery = clone $journeyQuery;
        $pages = new Pagination(['totalCount' => $countQuery->count()]);
        $journey = $journeyQuery->offset($pages->offset)
            ->limit($pages->limit)
            ->orderBy(['`j`.update_time' => SORT_DESC, '`j`.create_time' => SORT_DESC])
            ->asArray()
            ->all();

        $meta = array('totalCount' => (int)$pages->totalCount, 'perPage' => $pages->getPageSize(), 'pageCount' => $pages->getPageCount(), 'currentPage' => $pages->getPage() + 1);
        $result = array('item' => $journey, '_meta' => $meta);

        $this->success($result);
    }

    /**
     * 新增报名
     * @throws \yii\db\Exception
     */
    public function actionCreate()
    {
        $time = time();
        $post = Yii::$app->request->post();

        $journey_id = is_numeric($post['journey_id']) ? $post['journey_id'] : 0;

        if (!$journey_id) {
            $this->response(Common::getPackedResponse(DATABASE_FAILED, 'DATABASE_FAILED'));
        }

        $journeyModelClass = new Journey();
        $journeyFields = [
            'j.journey_id',
            'max',
            'number' => 'COUNT(1)'
        ];
        $journey = $journeyModelClass::find()->from('{{%journey}} as j')->select($journeyFields)
            ->leftJoin('{{%apply}} as a', 'a.journey_id = j.journey_id')
            ->where(['j.journey_id' => $journey_id, 'active' => $this->active, 'j.is_deleted' => $this->notDeleted, 'a.is_deleted' => $this->notDeleted])
            ->asArray()
            ->one();

        if (!$journey) {
            $this->response(Common::getPackedResponse(DATABASE_FAILED, 'DATABASE_FAILED'));
        }

        if ($journey['number'] >= $journey['max']) {
            $this->response(Common::getPackedResponse(DATABASE_FAILED, 'DATABASE_FAILED'));
        };

        $applyModelClass = new Apply();
        $apply = $applyModelClass::findOne(['credentials_no' => $post['credentials_no'], 'is_deleted' => $this->notDeleted]);

        if ($apply) {
            $this->response(Common::getPackedResponse(DATABASE_FAILED, 'DATABASE_FAILED'));
        }

        $applyModelClass->load($post, '');
        $applyModelClass->journey_id = $post['journey_id'];
        $applyModelClass->mobile = $post['mobile'];
        $applyModelClass->create_time = $time;


        $tr = Yii::$app->db->beginTransaction();
        try {

            if (!$applyModelClass->save()) {
                throw new \Exception('DATABASE_FAILED');
            }

            if ($post['gift_id']) {
                $applyGiftModelClass = new ApplyGift();
                $applyGiftModelClass->load($post, '');
                $applyGiftModelClass->apply_id = $applyModelClass->apply_id;

                if (!$applyGiftModelClass->save()) {
                    throw new \Exception('DATABASE_FAILED');
                }
            }

            $tr->commit();

        } catch (\Exception $e) {
            $tr->rollBack();
            $this->response(Common::getPackedResponse(DATABASE_FAILED, Yii::t('app', $e->getMessage())));
        }

        $this->success(array('id' => (string)$applyModelClass->apply_id));
    }

    /**
     * 报名详情
     */
    public function actionView()
    {
        $get = Yii::$app->request->get();

        $apply_id = is_numeric($get['id']) ? $get['id'] : 0;

        $applyModelClass = new Apply();
        $applyFields = [
            'a.apply_id',
            'a.journey_id',
            'name',
            'mobile',
            'credentials',
            'credentials_no',
            'born',
            'gender',
            'ag.gift_id'
        ];
        $apply = $applyModelClass::find()->from('{{%apply}} as a')->select($applyFields)
            ->leftJoin('{{%apply_gift}} as ag', 'ag.apply_id = a.apply_id')
            ->where(['a.apply_id' => $apply_id, 'a.is_deleted' => $this->notDeleted])
            ->asArray()
            ->one();

        if (!$apply) {
            $this->response(Common::getPackedResponse(DATABASE_FAILED, 'DATABASE_FAILED'));
        }

        $data = array('apply' => $apply);
        $this->success($data);
    }

    /**
     * 报名更新
     * @throws \yii\db\Exception
     */
    public function actionUpdate()
    {
        $get = Yii::$app->request->get();
        $post = Yii::$app->request->post();

        $apply_id = is_numeric($get['id']) ? $get['id'] : 0;

        if (empty($apply_id)) {
            $this->response(Common::getPackedResponse(DATABASE_FAILED, Yii::t('app', '{attribute} does not exist!', ['attribute' => Yii::t('app', 'apply_id')])));
        }

        $applyModelClass = new Apply();
        $isExistCredentialsNo = $applyModelClass::findOne(['credentials_no' => $post['credentials_no'], 'is_deleted' => $this->notDeleted]);

        if (isset($isExistCredentialsNo['apply_id']) && intval($isExistCredentialsNo['apply_id']) !== intval($apply_id)) {
            $this->response(Common::getPackedResponse(DATABASE_FAILED, Yii::t('app', '{attribute} exist!', ['attribute' => Yii::t('app', 'credentials_no')])));
        }

        $apply = $applyModelClass::findOne(['apply_id' => $apply_id, 'is_deleted' => $this->notDeleted]);

        if (empty($apply)) {
            $this->response(Common::getPackedResponse(DATABASE_FAILED, Yii::t('app', '{attribute} does not exist!', ['attribute' => Yii::t('app', 'apply')])));
        }

        $apply->setAttributes($post);
        $apply->mobile = $post['mobile'];
        $apply->update_time = time();

        if (!$apply->validate()) {//验证数据
            $this->response(Common::getPackedResponse(DATABASE_FAILED, 'DATABASE_FAILED'));
        }

        $tr = Yii::$app->db->beginTransaction();//事务 如果失败 就会滚数据
        try {
            if (!$apply->update()) {
                throw new \Exception('DATABASE_FAILED');
            }

            ApplyGift::deleteAll(['apply_id' => $apply_id]);

            if ($post['gift_id']) {
                $applyGiftModelClass = new ApplyGift();
                $applyGiftModelClass->load($post, '');
                $applyGiftModelClass->apply_id = $apply_id;

                if (!$applyGiftModelClass->save()) {
                    throw new \Exception('DATABASE_FAILED');
                }
            }

            $tr->commit();
        } catch (\Exception $e) {
            $tr->rollBack();
            $this->response(Common::getPackedResponse(DATABASE_FAILED, 'DATABASE_FAILED'));
        }

        $this->success(array('apply_id' => $apply_id));
    }

    /**
     * 删除报名
     * @throws \yii\db\Exception
     */
    public function actionDelete()
    {
        $get = Yii::$app->request->get();

        $apply_id = is_numeric($get['id']) ? $get['id'] : 0;

        $applyModelClass = new Apply();
        $apply = $applyModelClass::findOne(['apply_id' => $apply_id, 'is_deleted' => $this->notDeleted]);

        if (empty($apply)) {
            $this->response(Common::getPackedResponse(DATABASE_FAILED, 'DATABASE_FAILED'));
        }

        $apply->is_deleted = $this->deleted;

        $tr = Yii::$app->db->beginTransaction();//事务 如果失败 就会滚数据
        try {

            if (!$apply->update()) {
                throw new \Exception('DATABASE_FAILED');
            }

            $tr->commit();
        } catch (\Exception $e) {
            $tr->rollBack();
            $this->response(Common::getPackedResponse(DATABASE_FAILED, 'DATABASE_FAILED'));
        }

        $this->success(array('apply_id' => $apply_id));
    }

    /**
     * 礼品
     */
    public function actionGift()
    {
        $get = Yii::$app->request->get();

        $journey_id = is_numeric($get['journey_id']) ? $get['journey_id'] : 0;

        $journeyGiftModelClass = new JourneyGift();
        $giftsFields = [
            'value' => 'g.gift_id',
            'label' => 'g.gift_name'
        ];
        $gifts = $journeyGiftModelClass::find()->from('{{%gift}} as g')->select($giftsFields)
            ->leftJoin('{{%journey_gift}} as jg', 'jg.gift_id = g.gift_id')
            ->where(['g.active' => $this->active, 'g.is_deleted' => $this->notDeleted, 'jg.journey_id' => $journey_id])
            ->asArray()
            ->all();

        $data = [
            'gifts' => $gifts,
        ];

        $this->success($data);
    }

    /**
     * 上传Excel
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \yii\db\Exception
     */
    public function actionUpload()
    {
        $post = Yii::$app->request->post();

        if (!isset($post['journey_id']) || !is_numeric($post['journey_id'])) {
            $this->response(Common::getPackedResponse(DATABASE_FAILED, 'DATABASE_FAILED'));
        }

        $journeyModelClass = new Journey();
        $journey = $journeyModelClass::findOne(['journey_id' => $post['journey_id'], 'active' => $this->active, 'is_deleted' => $this->notDeleted]);

        if (!$journey) {
            $this->response(Common::getPackedResponse(DATABASE_FAILED, 'DATABASE_FAILED'));
        }

        $model = new UploadForm();

        if (Yii::$app->request->isPost) {
            $model->uploadFile = UploadedFile::getInstances($model, 'uploadFile');

            $fileArray = array();
            if ($model->uploadFile && $model->validate()) {
                foreach ($model->uploadFile as $file) {
                    $folder = 'uploads/' . date("Y") . '/' . date("m") . '/' . date("d");

                    $extension = substr(strrchr($file, '.'), 1);

                    $filename = uniqid() . '.' . $extension;
                    $this->fileExists(Yii::$app->basePath . '/web', $folder);
                    $path = $folder . '/' . time() . $filename;

                    $file->saveAs($path);
                    $fileArray[] = $path;
                }
                if (!empty($fileArray)) {
                    $applyModelClass = new Apply();
                    $oldApplyArray = $applyModelClass::find()->where(['journey_id' => $post['journey_id'], 'is_deleted' => $this->notDeleted])->asArray()->all();

                    $applyKey = ['journey_id', 'name', 'credentials', 'credentials_no', 'born', 'gender', 'mobile', 'gift_id', 'create_time'];

                    $applyArray = self::importExcel($fileArray, $post['journey_id']);

                    $newApplyArray = $this->multiple_unique($oldApplyArray, $applyArray, 'credentials_no');

                    $res = \Yii::$app->db->createCommand()->batchInsert(Apply::tableName(), $applyKey, $newApplyArray)->execute();

                    $data = [
                        'count' => $res,
                        'exist_count' => count($applyArray) - count($newApplyArray)
                    ];

                    $this->success($data);
                }
            }
        }
    }

    /**
     * 去重复
     * @param $old
     * @param $new
     * @param $key
     * @return mixed
     */
    public function multiple_unique($old, $new, $key)
    {
        foreach ($old as $o) {
            $search_value = $o[$key];
            $result = array_filter($new, function ($t) use ($search_value) {
                return in_array($search_value, $t);
            });

            unset($new[key($result)]);
        }

        return $new;
    }

    /**
     * 导入Excel
     * @param $fileArray
     * @param $journey_id
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public function importExcel($fileArray, $journey_id)
    {
        $time = time();
        $reader = new Xlsx();

        $array = [];
        foreach ($fileArray as $file) {
            $spreadsheet = $reader->load($file);

            $worksheet = $spreadsheet->getActiveSheet();
            $highestRow = $worksheet->getHighestRow(); // 总行数

            $lines = $highestRow - 2;

            if ($lines <= 0) {
                $this->response(Common::getPackedResponse(DATABASE_FAILED, 'DATABASE_FAILED'));
            }

            $row = 2;

            for ($titleRow = 1; $titleRow < $row; ++$titleRow) {
                if ($worksheet->getCellByColumnAndRow(2, $titleRow)->getValue() !== '姓名' ||
                    $worksheet->getCellByColumnAndRow(3, $titleRow)->getValue() !== '证件类型' ||
                    $worksheet->getCellByColumnAndRow(4, $titleRow)->getValue() !== '证件号码' ||
                    $worksheet->getCellByColumnAndRow(5, $titleRow)->getValue() !== '出生日期' ||
                    $worksheet->getCellByColumnAndRow(6, $titleRow)->getValue() !== '性别' ||
                    $worksheet->getCellByColumnAndRow(7, $titleRow)->getValue() !== '联系电话' ||
                    $worksheet->getCellByColumnAndRow(8, $titleRow)->getValue() !== '礼品') {
                    $this->response(Common::getPackedResponse(DATABASE_FAILED, 'DATABASE_FAILED'));
                }
            }

            $giftModelClass = new JourneyGift();
            $giftArray = $giftModelClass::find()->from('{{%journey_gift}} as jg')->select('g.*')
                ->leftJoin('{{%gift}} as g', 'g.gift_id = jg.gift_id')
                ->where(['journey_id' => $journey_id])->asArray()->all();

            for (; $row <= $highestRow; ++$row) {
                $name = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); //姓名
                $credentials = $worksheet->getCellByColumnAndRow(3, $row)->getValue(); //证件
                $credentials_no = $worksheet->getCellByColumnAndRow(4, $row)->getFormattedValue(); //证件号码
                $born = $worksheet->getCellByColumnAndRow(5, $row)->getFormattedValue(); //出生日期
                $gender = $worksheet->getCellByColumnAndRow(6, $row)->getValue(); //性别
                $mobile = $worksheet->getCellByColumnAndRow(7, $row)->getValue(); //联系电话
                $gift_name = $worksheet->getCellByColumnAndRow(8, $row)->getValue(); //礼品

                $mobile = $mobile ? $mobile : '';

                $gift = array_column(array_filter($giftArray, function ($value) use ($gift_name) {
                    return $value['gift_name'] == $gift_name;
                }), 'gift_id');

                $gift_id = $gift ? $gift[0] : 0;

                $array[] = [$journey_id, $name, $credentials, $credentials_no, strtotime($born), $gender, $mobile, $gift_id, $time];
            }

            @unlink($file);
        }

        return $array;
    }

    /**
     * 文件是否存在
     * @param $uploadPath
     * @param $folder
     * @return string
     */
    public function fileExists($uploadPath, $folder)
    {
        $folderArr = explode('/', $folder);
        foreach ($folderArr as $key => $value) {
            $uploadPath = $uploadPath . '/' . $value;
            if (!file_exists($uploadPath)) {
                mkdir($uploadPath);
            }
        }

        return $uploadPath;
    }
}